﻿
package com.wit.dao.impl;

import com.wit.po.Employee;
import com.wit.po.Employeeaddition;
import com.wit.po.Employeeposition;
import com.wit.po.Policy;
import com.wit.po.Wage;
import com.wit.po.EmployeeRelation;

import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

import com.wit.dao.WageDao;

import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;
import hibernate.HibernateSessionFactory;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Property;
import org.hibernate.criterion.Restrictions;
import org.hibernate.criterion.SimpleExpression;

public class WageDaoImpl implements WageDao {
	
	public boolean addWage(Wage wage)
	{
		Session session = null;
		boolean flag = false;
		try {
			session = HibernateSessionFactory.getSession();
			Transaction tx = session.beginTransaction();
			session.save(wage);
			tx.commit();
			flag = true;
		
		} catch (Exception e) {

			flag = false;
		} finally {

			if(session != null && session.isOpen())
				session.close();
		}
		return flag;
	}
	
	public boolean removeWage(Wage wage)
	{
		Session session = null;
		boolean flag = false;
		try {
			session = HibernateSessionFactory.getSession();
			Transaction tx = session.beginTransaction();
			session.delete(wage);
			tx.commit();
			flag = true;
		
		} catch (Exception e) {

			flag = false;
		} finally {

			if(session != null && session.isOpen())
				session.close();
		}
		return flag;
	}
	
	public boolean updateWage(Wage wage)
	{
		Session session = null;
		boolean flag = false;
		try {
			session = HibernateSessionFactory.getSession();
			Transaction tx = session.beginTransaction();
			
			session.update(wage);
			
			tx.commit();
			flag = true;
		
		} catch (Exception e) {

			flag = false;
		} finally {

			if(session != null && session.isOpen())
				session.close();
		}
		return flag;
	}
	
	public Wage getWageById(int wageId)
	{
		String hql = "from Wage wage where wage.wageId=?";
		Session session = null;
		Wage wage = null;
		try {
			session = HibernateSessionFactory.getSession();
			Query query = session.createQuery(hql);
			
			query.setInteger(0, wageId);
			wage = (Wage)query.uniqueResult();
			
		} catch (Exception e) {

			e.printStackTrace();
		} finally {

			if(session != null && session.isOpen())
				session.close();
		}
		return wage;
	}
	
	public boolean isWageExists(Session session, Wage wage) throws Exception
	{
		if(wage == null || wage.getEmployee() == null || wage.getEmployee().getEmployeeId() == null || wage.getStandardDate() == null)
		{
			return true;
		}
		String hql = "select count(*) from Wage wage where wage.employee.employeeId=? and YEAR(wage.standardDate)=? and MONTH(wage.standardDate)=?";
		boolean flag = false;
		
		Query query = session.createQuery(hql);
		
		Calendar calendar = Calendar.getInstance();
		calendar.setTime(wage.getStandardDate());
		
		query.setString(0, wage.getEmployee().getEmployeeId());
		query.setInteger(1, calendar.get(Calendar.YEAR));
		query.setInteger(2, calendar.get(Calendar.MONTH) + 1);
		
		if((Long)query.uniqueResult() > 0)
		{
			flag = true;
		}
		return flag;
	}

	private String buildHql(Wage wage)
	{
		StringBuilder hql =new StringBuilder("from Wage wage");
		if(wage == null)
			return hql.toString();
		Boolean wageState = wage.getWageState();
		Double minWage = wage.getWageDeserved();
		Double maxWage = wage.getWageActual();
		//////////////////////////////////////////////////
		Date startDate = wage.getStandardDate();			//应发放时间
		Date endDate = wage.getPayDate();
		
		String employeeId = null;
		String employeeName = null;
		String employeePosId = null;
		String employeeState = null;
		if(wage.getEmployee() != null)
		{
			employeeId = wage.getEmployee().getEmployeeId();
			employeeName = wage.getEmployee().getEmployeeNameC();
			employeePosId = wage.getEmployee().getEmployeeposition().getEmployeePosId();
			employeeState = wage.getEmployee().getEmployeeStatu();
		}
		boolean flag = true;
		if(employeeId != null && employeeId.trim().length() > 0)
		{
			hql.append(" where wage.employee.employeeId='"+employeeId+"'");
			flag = false;
		}
		if(wageState != null)
		{
			if(flag)
			{
				hql.append(" where wage.wageState="+wageState);
				flag = false;
			}
			else
			{
				hql.append(" and wage.wageState="+wageState);
			}
		}
		
		if(minWage != null)
		{
			if(flag)
			{
				hql.append(" where wage.wageDeserved>="+minWage);
				flag = false;
			}
			else
			{
				hql.append(" and wage.wageDeserved>="+minWage);
			}
		}
		if(maxWage != null)
		{
			if(flag)
			{
				hql.append(" where wage.wageDeserved<="+maxWage);
				flag = false;
			}
			else
			{
				hql.append(" and wage.wageDeserved<="+maxWage);
			}
		}
		if(startDate != null)
		{
			Calendar calendar = Calendar.getInstance();
			calendar.setTime(startDate);
			if(flag)
			{
				hql.append(" where YEAR(wage.standardDate)>="+calendar.get(Calendar.YEAR)+" and MONTH(wage.standardDate)>="+(calendar.get(Calendar.MONTH)+1));
				flag = false;
			}
			else
			{
				hql.append(" and YEAR(wage.standardDate)>="+calendar.get(Calendar.YEAR)+" and MONTH(wage.standardDate)>="+(calendar.get(Calendar.MONTH)+1));
			}
		}
		if(endDate != null)
		{
			Calendar calendar = Calendar.getInstance();
			calendar.setTime(endDate);
			if(flag)
			{
				hql.append(" where YEAR(wage.standardDate)<="+calendar.get(Calendar.YEAR)+" and MONTH(wage.standardDate)<="+(calendar.get(Calendar.MONTH)+1));
				flag = false;
			}
			else
			{
				hql.append(" and YEAR(wage.standardDate)<="+calendar.get(Calendar.YEAR)+" and MONTH(wage.standardDate)<="+(calendar.get(Calendar.MONTH)+1));
			}
		}
		if(employeeName != null && employeeName.trim().length() > 0)
		{
			if(flag)
			{
				hql.append(" where (wage.employee.employeeNameC='"+employeeName+"' or wage.employee.employeeNameE='"+employeeName+"')");
				flag = false;
			}
			else
			{
				hql.append(" and (wage.employee.employeeNameC='"+employeeName+"' or wage.employee.employeeNameE='"+employeeName+"')");
			}
		}
		if(employeeState != null && employeeState.trim().length() > 0)
		{
			if(flag)
			{
				hql.append(" where wage.employee.employeeStatu='"+employeeState+"'");
				flag = false;
			}
			else
			{
				hql.append(" and wage.employee.employeeStatu='"+employeeState+"'");
			}
		}
		if(employeePosId != null && employeePosId.trim().length() > 0)
		{
			if(flag)
			{
				hql.append(" where wage.employee.employeeposition.employeePosId='"+employeePosId+"'");
				flag = false;
			}
			else
			{
				hql.append(" and wage.employee.employeeposition.employeePosId='"+employeePosId+"'");
			}
		}
		if(wage.getEmployeeaddition() != null)
		{
			String wageCard = wage.getEmployeeaddition().getWageCard();
			if( wageCard != null && wageCard.trim().length() > 0)
			{
				if(flag)
				{
					hql.append(" where wage.employeeaddition.wageCard='"+wage.getEmployeeaddition().getWageCard()+"'");
					flag = false;
				}
				else
				{
					hql.append(" and wage.employeeaddition.wageCard='"+wage.getEmployeeaddition().getWageCard()+"'");
				}
			}
		}
		return hql.toString();
	}
	
	public List<Wage> getWageList(Wage wage, Integer pageNumber, Integer pageSize)
	{
		Session session = null;
		List<Wage> wageList = null;
		try {
			String hql = buildHql(wage);
			
			session = HibernateSessionFactory.getSession();
			Query query = session.createQuery(hql);
			if(pageNumber > 1 && pageSize >= 0)
			{
				query.setFirstResult((pageNumber - 1) * pageSize);
				query.setMaxResults(pageSize);
			}
			
			wageList = query.list();
		} catch (Exception e) {

			e.printStackTrace();
		} finally {

			if(session != null && session.isOpen())
				session.close();
		}
		return wageList;
	}

	public int getTotalAmount(Wage wage)
	{
		Session session = null;
		int totalAmount = 0;
		try {
			String hql = buildHql(wage);
			hql = "select count(*) " + hql;
			session = HibernateSessionFactory.getSession();
			Query query = session.createQuery(hql);
			
			totalAmount = ((Long)query.uniqueResult()).intValue();
			
		} catch (Exception e) {

			e.printStackTrace();
		} finally {

			if(session != null && session.isOpen())
				session.close();
		}
		return totalAmount;
	}
	
	public List<Wage> getWageList2(Wage wage, Integer pageNumber, Integer pageSize)
	{
		Session session = null;
		List<Wage> wageList = null;
		try {
			session = HibernateSessionFactory.getSession();
			
			Criteria criteria = session.createCriteria(Wage.class);
			
			String employeeId = wage.getEmployee().getEmployeeId();
			Boolean wageState = wage.getWageState();
			Double minWage = wage.getWageDeserved();
			Double maxWage = wage.getWageActual();
			//////////////////////////////////////////////////
			Date startDate = wage.getStandardDate();			//应发放时间
			Date endDate = wage.getPayDate();
			
			String employeeName = null;
			String employeePosId = null;
			if(wage.getEmployee() != null)
			{
				employeeName = wage.getEmployee().getEmployeeNameC();
				employeePosId = wage.getEmployee().getEmployeeposition().getEmployeePosId();
			}
			if(employeeId != null && employeeId.length() > 0)
				criteria.add(Property.forName("employee.employeeId").eq(employeeId));
			if(wageState != null)
				criteria.add(Property.forName("wageState").eq(wageState));
			
			if(minWage != null)
				criteria.add(Property.forName("wageDeserved").gt(minWage));
			if(maxWage != null)
				criteria.add(Property.forName("wageActual").lt(maxWage));
			if(startDate != null)
				criteria.add(Property.forName("standardDate").gt(startDate));
			if(endDate != null)
				criteria.add(Property.forName("standardDate").lt(endDate));
			if(employeeName != null)
			{
				criteria = criteria.createCriteria("employee").add(Restrictions.or(Restrictions.eq("employeeNameC", employeeName),Restrictions.eq("employeeNameE", employeeName)));
			}
			if(employeePosId != null)
				criteria.add(Property.forName("employee.employeePosId").eq(employeePosId));
			
			if(wage.getEmployeeaddition() != null && wage.getEmployeeaddition().getWageCard() != null)
			{
				criteria = criteria.createCriteria("employeeaddition").add(Restrictions.eq("wageCard", wage.getEmployeeaddition().getWageCard()));
			}
			
			criteria.setFirstResult((pageNumber-1)*pageSize);
			criteria.setMaxResults(pageSize);
			
			wageList = (List<Wage>)criteria.list();
			
		} catch (Exception e) {

			e.printStackTrace();
		} finally {

			if(session != null && session.isOpen())
				session.close();
		}
		return wageList;
			
	}
	
	public int getTotalAmount2(Wage wage)
	{
		Session session = null;
		Integer totalAmount = 0;
		try {
			session = HibernateSessionFactory.getSession();
			
			Criteria criteria = session.createCriteria(Wage.class);
			
			String employeeId = wage.getEmployee().getEmployeeId();
			Boolean wageState = wage.getWageState();
			Double wageDeserved = wage.getWageDeserved();
			Double wageActual = wage.getWageActual();
			//////////////////////////////////////////////////
			Date startDate = null;			//应发放时间
			Date endDate = null;
			
			String employeeName = null;
			String employeePosId = null;
			if(wage.getEmployee() != null)
			{
				employeeName = wage.getEmployee().getEmployeeNameC();
				employeePosId = wage.getEmployee().getEmployeeposition().getEmployeePosId();
			}
			if(employeeId != null && employeeId.length() > 0)
				criteria.add(Property.forName("employee.employeeId").eq(employeeId));
			if(wageState != null)
				criteria.add(Property.forName("wageState").eq(wageState));
				
			if(wageDeserved != null)
				criteria.add(Property.forName("wageDeserved").eq(wageDeserved));
			if(wageActual != null)
				criteria.add(Property.forName("wageActual").eq(wageActual));
			if(startDate != null)
				criteria.add(Property.forName("standardDate").gt(startDate));
			if(endDate != null)
				criteria.add(Property.forName("standardDate").lt(endDate));
			if(employeeName != null)
			{
				criteria.add(Property.forName("employee.employeeNameC").eq(employeeName)); 
								//|| Property.forName("employee.employeeNameE").eq(employeeName));
			}
			if(employeePosId != null)
				criteria.add(Property.forName("employee.employeePosId").eq(employeePosId));
				
			totalAmount = (Integer)criteria.setProjection(Projections.rowCount()).uniqueResult();
			
		} catch (Exception e) {

			e.printStackTrace();
		} finally {

			if(session != null && session.isOpen())
				session.close();
		}
		return totalAmount;
	}
	
	private List getPositionList()
	{
		String hql = "from Employeeposition";
		Session session = null;
		List<Employeeposition> positionList = null;
		try {
			session = HibernateSessionFactory.getSession();
			Query query = session.createQuery(hql);
	
			positionList = query.list();
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
		
			if(session != null && session.isOpen())
				session.close();
		}
		return positionList;
	}
	
	private Employeeposition getPosition(String positionId, List<Employeeposition> positionList)
	{
		if(positionId == null || positionList == null)
			return null;
		Employeeposition position = null;
		for(int i = positionList.size()-1; i >= 0; i --)
		{
			if(positionId.equals(positionList.get(i).getEmployeePosId()))
			{
				position = positionList.get(i);
				break;
			}
		}
		return position;
	}
	
	/*
	 *   计算  养老公积金
	 */
	public void calculatePension(Map<String, EmployeeRelation> employeeMap, List<Employeeposition> positionList, Map<String, Wage> emplWageMap, List fypAndFybList)
	{
		EmployeeRelation relation = null;
		Employeeposition position = null;
		Wage wage =null;
		Double fyb,fyc,pension;
		String employeeId;
		
		if(fypAndFybList != null)
		{
			for(int i = fypAndFybList.size() - 1; i >= 0; i --)
			{
				employeeId = (String)(((Object[])(fypAndFybList.get(i)))[0]);
				fyb = (Double)(((Object[])(fypAndFybList.get(i)))[1]);
				relation = employeeMap.get(employeeId);
				pension = relation.getPension();
				if(pension == null || pension < 0)
					pension = 0.0;
				wage = emplWageMap.get(employeeId);
				position = getPosition(relation.getEmployeePosId(), positionList);
				if(position != null)
				{
					if(position.getYrateC() != null && position.getYrateC() > 0)
					{
						relation.setPension(position.getYrateC() * fyb + pension);					// 业务层 公司提取部分
					}
				}
			}
		}
		
		Set<String> set = employeeMap.keySet();
		for(String o : set)
		{
			relation = employeeMap.get(o);
			pension = relation.getPension();
			if(pension == null || pension < 0)
				pension =0.0;
			wage = emplWageMap.get(o);
			position = getPosition(relation.getEmployeePosId(), positionList);
			if(position != null)
			{
				if(wage != null)
				{
					fyc = wage.getFyc();
					if (fyc != null && position.getYrateE() != null && fyc > 0 && position.getYrateE() > 0)
						pension += fyc * position.getYrateE();										//  业务层 个人提取部分
				}
				
				fyb = relation.getDirectGroupFYB();
				if (fyb != null && fyb > 0)
				{
					if (position.getGrateE() != null && position.getGrateE() > 0)
						pension += fyb * position.getGrateE();									//  管理层 自提取部分
					if(position.getGrateZ() != null && position.getGrateZ() > 0)
						pension += fyb * position.getGrateZ();									//  管理层 公司提取部分
				}
				relation.setPension(pension);
			}
		}
	}
	
	public void calculateAllFypAndFyb(Map<String, EmployeeRelation> employeeMap, List fypAndFybList, List<Employeeposition> positionList, Date inMonth)
	{
		EmployeeRelation relation = null;
		String employeeId = null;
		Double fyp = null;
		Double fyb = null;
		String ddirecterId = null;
		String bdirecterId = null;
		Double tmp = null;
		Employeeposition position = null;
		
		for(int i = fypAndFybList.size() - 1; i >= 0; i --)
		{
			employeeId = (String)(((Object[])fypAndFybList.get(i))[0]);
			fyb = (Double)(((Object[])fypAndFybList.get(i))[1]);
			fyp = (Double)(((Object[])fypAndFybList.get(i))[2]);
			
			relation = employeeMap.get(employeeId);
/*			
			tmp = relation.getDirectGroupFYP();
			if(tmp == null || tmp <= 0)
				tmp = 0.0;
			relation.setDirectGroupFYP(fyp + tmp);									// 加上 自身的 FYP
			tmp = relation.getDirectGroupFYB();
			if(tmp == null || tmp <= 0)
				tmp = 0.0;
			relation.setDirectGroupFYB(fyb + tmp);									// 加上 自身的 FYB
*/
			
			position = null;
			
			while(relation != null)			// 当且仅当自身职位没有直辖组时才向上
			{
				ddirecterId = relation.getDdirecterId();
				
				position = getPosition(relation.getEmployeePosId(), positionList);
				if(position == null || position.hasGroup())
					break;
				
				if(ddirecterId != null)
					relation = employeeMap.get(ddirecterId);
				else
					relation = null;
			}
			if(position != null && position.hasGroup())
			{
				tmp = relation.getDirectGroupFYP();
				if(tmp == null)
					tmp = 0.0;
				relation.setDirectGroupFYP(tmp + fyp);							// 直辖组FYP
				tmp = relation.getDirectGroupFYB();
				if(tmp == null)
					tmp = 0.0;
				relation.setDirectGroupFYB(tmp + fyb);							//直辖组FYB
			}
		}
		//////////////////////////////////////////////////////// ADD CODE FROM HERE   所辖组 FYP、FYB
		boolean hasDepartment = false;
		Set<String> set = employeeMap.keySet();
		for(String o : set)
		{
			relation = employeeMap.get(o);
			bdirecterId = relation.getBdirecterId();

			fyb = relation.getDirectGroupFYB();
			fyp = relation.getDirectGroupFYP();
			if(fyp == null)
				fyp = 0.0;
			position = getPosition(relation.getEmployeePosId(), positionList);
			if(fyb != null && fyb > 0 && position != null && position.hasGroup())
			{
				boolean isRightMonth = relation.isRightMonthForBallowance(inMonth);
				EmployeeRelation t_relation;
				if(isRightMonth && bdirecterId != null && (t_relation = employeeMap.get(bdirecterId)) != null && !position.hasDepartment())
				{
					tmp = t_relation.getDirectYcGroupFYB();
					if(tmp == null || tmp < 0)
						tmp = 0.0;
					t_relation.setDirectYcGroupFYB(tmp + fyb);										// 直接育成的理财经理的FYB
				}
				hasDepartment = position.hasDepartment();
				while(bdirecterId != null && (relation = employeeMap.get(bdirecterId)) != null)
				{
					bdirecterId = relation.getBdirecterId();
					position = getPosition(relation.getEmployeePosId(), positionList);
					if(position != null)												// position 为空 则不再上溯
					{
						if(isRightMonth)
						{
							if(!hasDepartment)												// 不是区域经理
							{
								tmp = relation.getYcGroupFYB();
								if(tmp == null)
									tmp = 0.0;
								relation.setYcGroupFYB(fyb + tmp);								//所辖组FYB ，不含 直辖组
							}
							else															// 是区域经理
							{
								tmp = relation.getDirectDepartFYB();
								if(tmp == null)
									tmp = 0.0;
								relation.setDirectDepartFYB(fyb + tmp);								//所辖部FYB ，不含 直辖部，不含 间接育成的区域经理的FYP
								
								tmp = relation.getDirectDepartFYP();
								if(tmp == null)
									tmp = 0.0;
								relation.setDirectDepartFYP(fyp + tmp);								//所辖部FYP ，不含 直辖部
								
								break;								/////////////////////// 注意：因为只要  直接育成的区域经理的FYP和,所以不再上推
							}
						}
						if(!hasDepartment)												// 不是区域经理
						{
							tmp = relation.getIndirectGroupFYB();
							if(tmp == null)
								tmp = 0.0;
							relation.setIndirectGroupFYB(fyb + tmp);								//所辖组FYB ，不含 直辖组
							
							tmp = relation.getIndirectGroupFYP();
							if(tmp == null)
								tmp = 0.0;
							relation.setIndirectGroupFYP(fyp + tmp);								//所辖组FYP ，不含 直辖组
						}
						
						hasDepartment = position.hasDepartment();										// 判断是否是 区域经理
					}
					else
					{
						break;
					}
				}
			}
		}
	}
	
	private void calculateAnnualFypAndFyb(Map<String, EmployeeRelation> employeeMap, List yearAwardList, List<Employeeposition> positionList)
	{
		if(yearAwardList == null)
			return;
		EmployeeRelation relation = null;
		String employeeId = null;
		Double fyp = null;
		Double fyb = null;
		String ddirecterId = null;
		String bdirecterId = null;
		Double tmp = null;
		Employeeposition position = null;
		
		for(int i = yearAwardList.size() - 1; i >= 0; i --)
		{
			employeeId = (String)(((Object[])yearAwardList.get(i))[0]);
			fyb = (Double)(((Object[])yearAwardList.get(i))[1]);
			fyp = (Double)(((Object[])yearAwardList.get(i))[2]);
			
			relation = employeeMap.get(employeeId);
			position = null;
			
			while(relation != null)			// 当且仅当自身职位没有直辖组时才向上
			{
				ddirecterId = relation.getDdirecterId();
				position = getPosition(relation.getEmployeePosId(), positionList);
				
				if(position == null || position.hasGroup())
					break;
				if(ddirecterId != null)
					relation = employeeMap.get(ddirecterId);
				else
					relation = null;
			}
			if(position != null && position.hasGroup())
			{
				tmp = relation.getGroupAnnualFYP();
				if(tmp == null)
					tmp = 0.0;
				relation.setGroupAnnualFYP(tmp + fyp);							// 直辖组FYP
				tmp = relation.getGroupAnnualFYB();
				if(tmp == null)
					tmp = 0.0;
				relation.setGroupAnnualFYB(tmp + fyb);							//直辖组FYB
			}
		}
		//////////////////////////////////////////////////////////////
		boolean hasDepartment = false;
		Set<String> set = employeeMap.keySet();
		for(String o : set)
		{
			relation = employeeMap.get(o);
			bdirecterId = relation.getBdirecterId();

			fyb = relation.getGroupAnnualFYB();
			fyp = relation.getGroupAnnualFYP();
			if(fyp == null)
				fyp = 0.0;
			position = getPosition(relation.getEmployeePosId(), positionList);
			if(position != null && !position.hasDepartment() && fyb != null && fyb > 0)
			{
				while(position != null && position.hasGroup() && bdirecterId != null && (relation = employeeMap.get(bdirecterId)) != null)
				{
					bdirecterId = relation.getBdirecterId();
					position = getPosition(relation.getEmployeePosId(), positionList);
					
					if(position != null && position.hasDepartment())
					{
						tmp = relation.getGroupAnnualFYB();
						if(tmp == null || tmp <= 0)
							tmp =0.0;
						relation.setGroupAnnualFYB(fyb + tmp);
						
						tmp = relation.getGroupAnnualFYP();
						if(tmp == null || tmp <= 0)
							tmp =0.0;
						relation.setGroupAnnualFYP(fyp + tmp);
						break;																	// 遇到  区域经理  则不再上推，因为再上推将不是直辖部，而是所辖部
					}
				}
			}
		}
	}
	/*
	 * 计算年度分红
	 */
	private void calculateAnnualDividend(Map<String, EmployeeRelation> employeeMap, Map<String, Wage> emplWageMap, 
										List<Employeeposition> positionList, List yearAwardList)
	{
		calculateAnnualFypAndFyb(employeeMap, yearAwardList,positionList);
		
		EmployeeRelation relation = null;
		Wage wage = null;
		Double fyp,fyb;
		Double tmp = null;
		Employeeposition position = null;
		
		Set<String> set = employeeMap.keySet();
		for(String o : set)
		{
			relation = employeeMap.get(o);
			
			fyp = relation.getGroupAnnualFYP();
			fyb = relation.getGroupAnnualFYB();
			if(fyp == null)
				fyp = 0.0;
			if(fyb != null && fyb > 0 && (position = getPosition(relation.getEmployeePosId(),positionList)) != null)
			{
				if(position.hasDepartment())
				{
					wage = emplWageMap.get(o);
					if(fyp > Wage.DirectDepartFypBoundaries[0] && position.getAreaSrate2() != null && position.getAreaSrate2() > 0)
					{
						wage.setAnnualDividend(fyb * position.getAreaSrate2());
					}
					else if(fyp > Wage.DirectDepartFypBoundaries[1] && position.getAreaSrate1() != null && position.getAreaSrate1() > 0)
					{
						wage.setAnnualDividend(fyb * position.getAreaSrate1());
					}
				}
			}
		}
	}
	
	public Map<String,EmployeeRelation> getEmployeeRelationMap(Session session) throws Exception
	{
		String hql = "select new com.wit.po.EmployeeRelation(e.employeeId,e.employeeposition.employeePosId,ea.ddirecterId,ea.bdirecterId,ea.zycompetent,ea.dallowanceDate,ea.ballowanceDate) "+
						"from Employee e, com.wit.po.Employeeaddition ea where e.employeeId=ea.employeeId";
		HashMap<String,EmployeeRelation> map = null;
		
		Query query = session.createQuery(hql);

		List<EmployeeRelation> list = query.list();
		
		int size = list.size();
		map = new HashMap<String,EmployeeRelation>();
		for(int i=0;i<size;i++)
		{
			map.put(list.get(i).getEmployeeId(), list.get(i));
		}
		return map;
	}
	
	private void calculateRealFyb(List list, Map<String, EmployeeRelation> employeeMap, List<Employeeposition> positionList, int employeeIdIndex, int fybIndex)
	{
		if(list == null || list.size() == 0 || positionList == null)
			return;
		Object[] o = (Object[])list.get(0);
		if(fybIndex >= o.length || employeeIdIndex >= o.length)
			return;
		String employeeId;
		Double fyb;
		Employeeposition position;
		EmployeeRelation relation;
		for(int i = list.size() - 1; i >=0; i --)
		{
			o = (Object[])(list.get(i));
			employeeId = (String)o[employeeIdIndex];
			fyb = (Double)o[fybIndex];
			relation = employeeMap.get(employeeId);
			if(fyb != null && fyb > 0 && relation != null && (position = getPosition(relation.getEmployeePosId(),positionList)) != null)
			{
				if(position.getFirstrate() != null && position.getFirstrate() > 0)
				{
					o[fybIndex] = fyb * position.getFirstrate();
				}
			}
		}
	}
	
	// other criterias
	/**
	 *	inMonth: 批量生成工资记录的月份，仅有年、月有效
	 *
	 */
	public int generateWage(Date inMonth)
	{
		if(inMonth == null)
			return 0;
		
		System.out.println("BEGIN!!!");
		
		Session session = null;
		Map<String,EmployeeRelation> employeeMap = null;	/**包含所有员工 */
		Map<String,Wage> emplWageMap = null;				/**包含所有员工 */
		List<Employeeposition> positionList = null;			/**所有职位列表 */
		
		List fypAndFybList = null;							/**不一定包含所有员工 */
		List initialCommissionList = null;					/**不一定包含所有员工 */
		List renewalCommissionList = null;					/**不一定包含所有员工 */
		List yearAwardList = null;							/**不一定包含所有员工 */

		Calendar calendar = Calendar.getInstance();
		calendar.setTime(inMonth);
		int year = calendar.get(Calendar.YEAR);
		int month = calendar.get(Calendar.MONTH) + 1;
		// 初佣		3
		String hql1 = "select policy.employee.employeeId,sum(policy.sumFee),sum(policy.sumFee*policy.insurancedef.agencyrate) "+
						"from Policy policy "+		// where policy.employee.enterDate<?
						"where policy.isEffect=true and policy.isCountinue=false and YEAR(policy.insuranceDate)=? and MONTH(policy.insuranceDate)=? "+
						"group by policy.employee.employeeId";
		// 续佣		2
		String hql2 = "select policy.employee.employeeId,sum(policy.sumFee) " +
						"from Policy policy " +
						"where policy.isEffect=true and policy.isCountinue=true and YEAR(policy.insuranceDate)=? and MONTH(policy.insuranceDate)=? "+
						"group by policy.employee.employeeId";
		
		// 季度奖	3
		String hql3 = "select policy.employee.employeeId,sum(policy.sumFee*policy.insurancedef.agencyrate) " +
						"from Policy policy " +
						"where policy.isEffect=true and YEAR(policy.insuranceDate)=? and MONTH(policy.insuranceDate) between ? and ? "+
						"group by policy.employee.employeeId";
		
		// 年度奖	1
		String hql4 = "select policy.employee.employeeId,sum(policy.sumFee*policy.insurancedef.agencyrate) " +
						",sum(sumFee*(case when policy.expendPeriod>=10 then 1 when policy.expendPeriod>=5 then 0.5 when policy.expendPeriod>=3 then 0.3 else 0.1 end)) "+
						"from Policy policy "+
						"where policy.isEffect=true and YEAR(policy.insuranceDate)=? "+
						"group by policy.employee.employeeId";
		
		String hql5 = "select policy.employee.employeeId,sum(policy.sumFee*policy.insurancedef.agencyrate) "+
						",sum(sumFee*(case when policy.expendPeriod>=10 then 1 when policy.expendPeriod>=5 then 0.5 when policy.expendPeriod>=3 then 0.3 else 0.1 end)) "+
						"from Policy policy "+
						"where policy.isEffect=true and policy.isCountinue=false and YEAR(policy.insuranceDate)=? and MONTH(policy.insuranceDate)=? "+
						"group by policy.employee.employeeId";
		
		String hql = "from Employeeposition";
		
		try {

			session = HibernateSessionFactory.getSession();
			Query query = session.createQuery(hql);
			positionList = query.list();
			
			employeeMap = getEmployeeRelationMap(session);
		
			if(employeeMap == null || positionList == null || inMonth == null)
				return 0;
			
			emplWageMap = new HashMap<String,Wage>();
			Set<String> set = employeeMap.keySet();
			for(String o : set)
			{
				Wage t_wage = new Wage();
				t_wage.setStandardDate(inMonth);
				emplWageMap.put(o, t_wage);
			}
			
			query = session.createQuery(hql5);
		//	query.setInteger(0, Employee.SEPARATER_DATE);
			query.setInteger(0, year);
			query.setInteger(1, month);
			
			fypAndFybList = query.list();													//每个员工的FYP
			calculateRealFyb(fypAndFybList, employeeMap, positionList, 0, 1);
			
		//	List initialCommissionList    employeeId,sum(sumFee),FYB
			query = session.createQuery(hql1);
		//	query.setInteger(0, Employee.SEPARATER_DATE);
			query.setInteger(0, year);
			query.setInteger(1, month);
			
			initialCommissionList = query.list();
			calculateRealFyb(initialCommissionList, employeeMap, positionList, 0, 2);
			
			
		//	List renewalCommissionList		employeeId,sum(Fee)
			query = session.createQuery(hql2);
		//	query.setInteger(0, Employee.SEPARATER_DATE);
			query.setInteger(0, year);
			query.setInteger(1, month);
			renewalCommissionList = query.list();
		
		// 月度销售奖

			String employeeId;
			String zycompetent;
			//String bdirecterId;
			String ddirecterId;
			Double fee;
			Double fyb;
			Double fyp;
			Employeeposition position = null;
			Wage wage;
			Double tmp = null;
			
			if(0 == month % 3)									// 3、6、9、12 月 则计算季度销售奖
			{
				// 季度销售奖
				// List quarterAwardList		employeeId,FYB
				List quarterAwardList = null;
				query = session.createQuery(hql3);
			//	query.setInteger(0, Employee.SEPARATER_DATE);
				query.setInteger(0, year);
				query.setInteger(1, month - 2);
				query.setInteger(2, month);
				quarterAwardList = query.list();
				calculateRealFyb(quarterAwardList, employeeMap, positionList, 0, 1);
				
				if(quarterAwardList != null)
				{
					for(int i = quarterAwardList.size() - 1; i >= 0; i --)
					{ 
						employeeId = ((Object[])quarterAwardList.get(i))[0].toString();
						fyb = (Double)((Object[])quarterAwardList.get(i))[1];
	
						position = getPosition(employeeMap.get(employeeId).getEmployeePosId(),positionList);
						if(position != null && position.getSeasonrate() != null && position.getSeasonrate() > 0)
						{
							wage = emplWageMap.get(employeeId);
							if(wage != null)
							{
								wage.setQuarterlyAward(fyb * position.getSeasonrate());			// + 季度销售奖
							}
						}
					}
				}
				if(12 == month)
				{
					// 年度销售奖、年度分红
					// List yearAwardList		employeeId,FYB
					query = session.createQuery(hql4);
				//	query.setInteger(0, Employee.SEPARATER_DATE);
					query.setInteger(0, year);
					yearAwardList = query.list();
					calculateRealFyb(yearAwardList, employeeMap, positionList, 0, 1);
					
					if(yearAwardList != null)
					{
						for(int i = yearAwardList.size() - 1; i >= 0; i --)
						{
							employeeId = ((Object[])yearAwardList.get(i))[0].toString();
							fyb = (Double)((Object[])yearAwardList.get(i))[1];
	
							position = getPosition(employeeMap.get(employeeId).getEmployeePosId(),positionList);
							if(position != null && position.getYearrate() != null && position.getYearrate() > 0)
							{
								wage = emplWageMap.get(employeeId);
								if(wage != null)
									wage.setAnnualAward(fyb * position.getYearrate());			// + 年度销售奖
							}
						}
						// 年度分红
						calculateAnnualDividend(employeeMap,emplWageMap,positionList,yearAwardList);
						
					}
				}
			}
			/************************* 数据库查询结束 ****************************/
			
			calculateAllFypAndFyb(employeeMap, fypAndFybList, positionList, inMonth);			// 计算employeeMap中的一些数据
			
			
			// 增员辅导津贴
			// 直接存到emplWageMap
			EmployeeRelation relation = null;
			for(int i = initialCommissionList.size()-1; i >= 0; i --)			//计算初佣等
			{
				employeeId = ((Object[])initialCommissionList.get(i))[0].toString();
				fee = (Double)((Object[])initialCommissionList.get(i))[1];
				fyb = (Double)((Object[])initialCommissionList.get(i))[2];
				
				if(employeeId == null || (relation = employeeMap.get(employeeId)) == null)
					continue;
				zycompetent = relation.getZycompetent();
				ddirecterId = relation.getDdirecterId();
				
				position = getPosition(employeeMap.get(employeeId).getEmployeePosId(),positionList);
				if(position != null && position.getFirstrate() != null && position.getFirstrate() > 0)
				{
					wage = emplWageMap.get(employeeId);
					if(wage != null)
					{
						wage.setFyc(fee * position.getFirstrate());							// + FYC
						
						if(position.getMonthrate() != null && position.getMonthrate() > 0)
							wage.setMonthlyAward(fyb * position.getMonthrate());			// + 月度销售奖      注意！  " * position.getFirstrate() " 不能少
					}

					// 增员主管的 增员辅导津贴
					if(relation.isRightMonthForDallowance(inMonth) && zycompetent != null && (relation = employeeMap.get(zycompetent)) != null)
					{
						position = getPosition(relation.getEmployeePosId(),positionList);
						if(position != null && position.getAddEmprate() != null && position.getAddEmprate() > 0)
						{
							wage = emplWageMap.get(zycompetent);
							if(wage != null)
							{
								tmp = wage.getZyAllowance();
								if(tmp == null)
									tmp = 0.0;
								wage.setZyAllowance(tmp + fyb * position.getAddEmprate());			// 增员主管的 增员辅导津贴
							}
						}
					}
				}
			}
			
			//	计算续佣
			for(int i = renewalCommissionList.size()-1; i >= 0; i --)			
			{
				employeeId = ((Object[])renewalCommissionList.get(i))[0].toString();
				fee = (Double)((Object[])renewalCommissionList.get(i))[1];
				relation = employeeMap.get(employeeId);
				wage = emplWageMap.get(employeeId);
				if(wage != null)
				{
					position = getPosition(relation.getEmployeePosId(),positionList);
					if(position != null && position.getConrate() != null && position.getConrate() > 0)
					{
						wage.setRyc(fee * position.getConrate());			// RYC
					}
				}
			}

			// 直辖组管理津贴
			for(String o : set)
			{
				relation = employeeMap.get(o);
				
				wage = emplWageMap.get(o);
				
				position = getPosition(relation.getEmployeePosId(),positionList);
				if(position != null)
				{
					fyp = relation.getDirectGroupFYP();
					fyb = relation.getDirectGroupFYB();									//fyb为直辖组FYB
					if(position.hasGroup() && fyb != null)													// 计算直辖组管理津贴
					{
						if(fyp > Wage.DirectGroupFypBoundaries[0] && position.getZrate5() != null && position.getZrate5() > 0)
						{
							wage.setDallowance(fyb * position.getZrate5());
						}
						else if(fyp > Wage.DirectGroupFypBoundaries[1] && position.getZrate4() != null && position.getZrate4() > 0)
						{
							wage.setDallowance(fyb * position.getZrate4());
						}
						else if(fyp > Wage.DirectGroupFypBoundaries[2] && position.getZrate3() != null && position.getZrate3() > 0)
						{
							wage.setDallowance(fyb * position.getZrate3());
						}
						else if(fyp > Wage.DirectGroupFypBoundaries[3] && position.getZrate2() != null && position.getZrate2() > 0)
						{
							wage.setDallowance(fyb * position.getZrate2());
						}
						else if(fyp > 0 && position.getZrate1() != null && position.getZrate1() > 0)
						{
							wage.setDallowance(fyb * position.getZrate1());
						}
					}
					fyp = relation.getIndirectGroupFYP();
					fyb = relation.getIndirectGroupFYB();									//fyb为所辖组FYB（不含直辖组）
					if(fyp ==null)
						fyp = 0.0;
				//	System.out.println(relation.getEmployeeId()+": "+fyb+","+fyp);
					if(position.hasDepartment() && fyb != null && fyb > 0)												// 计算    区域管理津贴 
					{
						if(fyp > Wage.IndirectGroupFypBoundaries[0] && position.getAreaMrate4() != null && position.getAreaMrate4() > 0)
						{
							wage.setQgAllowance(fyb * position.getAreaMrate4());
						}
						else if(fyp > Wage.IndirectGroupFypBoundaries[1] && position.getAreaMrate3() != null && position.getAreaMrate3() > 0)
						{
							wage.setQgAllowance(fyb * position.getAreaMrate3());
						}
						else if(fyp > Wage.IndirectGroupFypBoundaries[2] && position.getAreaMrate2() != null && position.getAreaMrate2() > 0)
						{
							wage.setQgAllowance(fyb * position.getAreaMrate2());
						}
						else if(fyp > 0 && position.getAreaMrate1() != null && position.getAreaMrate1() > 0)
						{
							wage.setQgAllowance(fyb * position.getAreaMrate1());
						}
					}
					
					fyb = relation.getDirectDepartFYB();
					if(position.hasDepartment() && fyb != null && fyb > 0)											//计算	区域育成奖
					{
						wage.setQycAllowance(fyb * position.getAreaY());
					}
				}
			}
			
			// 育成奖
			Double directYcFYB;
			Double firstY,secondY;
			for(String o : set)
			{
				relation = employeeMap.get(o);
				position = getPosition(relation.getEmployeePosId(), positionList);
				if(position != null && position.hasGroup())
				{
					directYcFYB = relation.getDirectYcGroupFYB();				// 直接 育成
					fyb = relation.getYcGroupFYB();						// 直接、间接 育成
					firstY = position.getFirstY();
					secondY = position.getSecondY();
					
					wage = emplWageMap.get(o);
					tmp = 0.0;
					if(directYcFYB != null && directYcFYB > 0)
					{
						if(firstY != null && firstY > 0)
						{
							tmp = directYcFYB * firstY;
						}
						if(secondY != null && secondY > 0)
						{
							tmp += (fyb - directYcFYB) * secondY;
						}
						wage.setYcAllowance(tmp);
					}
				}
			}
		
			calculatePension(employeeMap, positionList, emplWageMap, fypAndFybList);			// 计算 养老公积金
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {

			if(session != null && session.isOpen())
				session.close();
		}
		System.out.println("OVER!!!!");
		addBatchOfWage(emplWageMap);
		updatePension(employeeMap);
		System.out.println("ADD OVER!!!!");
		return 0;
		/*
		 *	FYC = 有效新契约保费 * FYC_XS
		 *	RYC = 续年度保费 * RYC_XS
		 *	FYB = FYC * FYB_XS
		 *	个人销售月度奖 = FYB * position.monthrate
		 *	业务层养老公积金 = FYC * 个人提取比例 + FYB * 公司提取比例
		 *	管理层养老公积金 = 直辖组FYB * 个人提取比例 + 直辖组FYB * 公司提取比例
		 *	增员辅导津贴
		 *	直辖组管理津贴
		 *	区域经理管理津贴
		 *	育成奖
		 *	区域育成奖
		 *	区域年度分红
		 */
	}
	
	private int addBatchOfWage(Map<String, Wage> emplWageMap)
	{
		if(emplWageMap == null)
			return 0;
		Session session = null;
		Set<String> set = emplWageMap.keySet();
		Wage wage;
		int counter = 0;
		try {
			
			session = HibernateSessionFactory.getSession();
			Transaction tx = session.beginTransaction();
			for(String o : set)
			{
				wage = emplWageMap.get(o);
				wage.setEmployee(new Employee(o));
				wage.setEmployeeaddition(new Employeeaddition(o));
				wage.check();
			//	System.out.println(wage.getFyc() + ",,," + wage.getWageDeserved());
				if(!isWageExists(session,wage) && wage.getWageDeserved() != null && wage.getWageDeserved() > 0)				// 确保每个员工每月只有一个工资记录
				{
					session.save(wage);
					counter ++;
				}
			}
			tx.commit();
			
		} catch (Exception e) {
			
			e.printStackTrace();
			counter = 0;
			
		} finally {
			
			if(session != null && session.isOpen())
				session.close();
		}
		
		return counter;
	}
	
	private boolean updatePension(Map<String, EmployeeRelation> employeeMap)
	{
		if(employeeMap == null)
			return false;
		Session session = null;
		boolean flag = false;
		Set<String> set = employeeMap.keySet();
		EmployeeRelation relation = null;
		Employeeaddition addition;
		String hql = "from Employeeaddition addition where employeeId=?";
		try {
			
			session = HibernateSessionFactory.getSession();
			Query query = session.createQuery(hql);
			
			Transaction tx = session.beginTransaction();
			for(String o : set)
			{
				relation = employeeMap.get(o);
				if(relation != null)
				{
					query.setString(0, relation.getEmployeeId());
					addition = (Employeeaddition)query.uniqueResult();
					if(addition != null && relation.getPension() != null)
					{
						addition.setPension(relation.getPension());
						session.update(addition);
					}
				}
			}
			tx.commit();
			flag = true;
			
		} catch (Exception e) {
			
			e.printStackTrace();
			flag = false;
			
		} finally {
			
			if(session != null && session.isOpen())
				session.close();
		}
		
		return flag;
	}

	
	public List<Wage> getWageListByIds(Integer []wageIdArray)
	{
		if(wageIdArray == null)
			return null;
		List<Wage> wageList = new ArrayList<Wage>();
		Session session = null;
		Wage wage;
		String hql = "from Wage wage where wage.wageId=?";
		try {

			session = HibernateSessionFactory.getSession();
			Query query = session.createQuery(hql);
			for(Integer o : wageIdArray)
			{
				query.setInteger(0, o);
				wage = (Wage)query.uniqueResult();
				if(wage != null)
				{
					wageList.add(wage);
				}
			}
		} catch (Exception e) {
			
			e.printStackTrace();
			
		} finally {
			
			if(session != null && session.isOpen())
				session.close();
		}
		return wageList;
	}
	
	
	public int updateBatchOfWage(List<Wage> wageList)
	{
		if(wageList == null)
			return 0;
		Session session = null;
		int counter = 0;
		try {
			
			session = HibernateSessionFactory.getSession();
			Date today = new Date();
			Transaction tx = session.beginTransaction();
			for(Wage wage : wageList)
			{
				if(wage != null && (wage.getWageState() == null || wage.getWageState() == false))
				{
					wage.setWageState(true);
					wage.setPayDate(today);
					wage.setWageActual(wage.getWageDeserved());
					session.update(wage);
					counter ++;
				}
			}
			tx.commit();
			
		} catch (Exception e) {
			
			e.printStackTrace();
			counter = 0;
			
		} finally {
			
			if(session != null && session.isOpen())
				session.close();
		}
		
		return counter;
	}
	
	/**
	 * employeeId : 员工编号
	 * year : 年份
	 */
	public Object[] getPolicyFee(String employeeId, int year)
	{
		boolean flag;
		String hql = null;
		if(employeeId == null || employeeId.trim().length() == 0)
		{
			hql = "select sum(policy.sumFee),count(*) from Policy policy where policy.isEffect=true and year(policy.insuranceDate)=?";
			flag = false;
		}
		else
		{
			hql = "select sum(policy.sumFee),count(*) from Policy policy where policy.isEffect=true and year(policy.insuranceDate)=? and policy.employee.employeeId=?";
			flag = true;
		}
		
		Object []o = null;
		Session session = null;
		try {

			session = HibernateSessionFactory.getSession();
			Query query = session.createQuery(hql);
			query.setInteger(0,year);
			if(flag)
				query.setString(1,employeeId);
			o = (Object [])query.uniqueResult();
			
		} catch (Exception e) {
			
			e.printStackTrace();
			
		} finally {
			
			if(session != null && session.isOpen())
				session.close();
		}
		return o;
	}
	
	public Object[] getPolicyFee(String employeeId, int year, int startMonth, int endMonth)
	{
		String hql = null;
		boolean flag;
		if(employeeId == null || employeeId.trim().length() == 0)
		{
			hql = "select sum(policy.sumFee),count(*) from Policy policy where policy.isEffect=true and year(policy.insuranceDate)=? and month(policy.insuranceDate) between ? and ?";
			flag = false;
		}
		else
		{
			hql = "select sum(policy.sumFee),count(*) from Policy policy where policy.isEffect=true and year(policy.insuranceDate)=? and month(policy.insuranceDate) between ? and ? and policy.employee.employeeId=?";
			flag = true;
		}

		Object[] o = null;
		Session session = null;
		try {

			session = HibernateSessionFactory.getSession();
			Query query = session.createQuery(hql);
			query.setInteger(0,year);
			query.setInteger(1,startMonth);
			query.setInteger(2,endMonth);
			if(flag)
				query.setString(3,employeeId);
			o = (Object[])query.uniqueResult();
			
		} catch (Exception e) {
			
			e.printStackTrace();
			
		} finally {
			
			if(session != null && session.isOpen())
				session.close();
		}
		return o;
	}
	
	public Object[] getPolicyFee(String employeeId, int year, int month)
	{
		String hql = null;
		boolean flag;
		if(employeeId == null || employeeId.trim().length() == 0)
		{
			hql = "select sum(policy.sumFee),count(*) from Policy policy where policy.isEffect=true and year(policy.insuranceDate)=? and month(policy.insuranceDate)=?";
			flag = false;
		}
		else
		{
			hql = "select sum(policy.sumFee),count(*) from Policy policy where policy.isEffect=true and year(policy.insuranceDate)=? and month(policy.insuranceDate)=? and policy.employee.employeeId=?";
			flag = true;
		}

		Object[] o = null;
		Session session = null;
		try {

			session = HibernateSessionFactory.getSession();
			Query query = session.createQuery(hql);
			query.setInteger(0,year);
			query.setInteger(1,month);
			if(flag)
				query.setString(2,employeeId);
			o = (Object[])query.uniqueResult();
			
		} catch (Exception e) {
			
			e.printStackTrace();
			
		} finally {
			
			if(session != null && session.isOpen())
				session.close();
		}
		return o;	
	}
	
	public List<Policy> getPolicyList(String employeeId, Integer year, Integer pageNumber, Integer pageSize)
	{
		boolean flag;
		String hql = null;
		if(employeeId == null || employeeId.trim().length() == 0)
		{
			hql = "from Policy policy where policy.isEffect=true and year(policy.insuranceDate)=?";
			flag = false;
		}
		else
		{
			hql = "from Policy policy where policy.isEffect=true and year(policy.insuranceDate)=? and policy.employee.employeeId=?";
			flag = true;
		}
		
		List<Policy> policyList = null;
		Session session = null;
		try {

			session = HibernateSessionFactory.getSession();
			Query query = session.createQuery(hql);
			query.setInteger(0,year);
			if(flag)
				query.setString(1,employeeId);
			
			query.setFirstResult((pageNumber - 1) * pageSize);
			query.setMaxResults(pageSize);
			
			policyList = (List<Policy>)query.list();
			
		} catch (Exception e) {
			
			e.printStackTrace();
			
		} finally {
			
			if(session != null && session.isOpen())
				session.close();
		}
		return policyList;
	}
	
	public List<Policy> getPolicyList(String employeeId, Integer year, Integer startMonth, Integer endMonth, 
															Integer pageNumber, Integer pageSize)
	{
		String hql = null;
		boolean flag;
		if(employeeId == null || employeeId.trim().length() == 0)
		{
			hql = "from Policy policy where policy.isEffect=true and year(policy.insuranceDate)=? and month(policy.insuranceDate) between ? and ?";
			flag = false;
		}
		else
		{
			hql = "from Policy policy where policy.isEffect=true and year(policy.insuranceDate)=? and month(policy.insuranceDate) between ? and ? and policy.employee.employeeId=?";
			flag = true;
		}

		List<Policy> policyList = null;
		Session session = null;
		try {

			session = HibernateSessionFactory.getSession();
			Query query = session.createQuery(hql);
			query.setInteger(0,year);
			query.setInteger(1,startMonth);
			query.setInteger(2,endMonth);
			if(flag)
				query.setString(3,employeeId);

			query.setFirstResult((pageNumber - 1) * pageSize);
			query.setMaxResults(pageSize);
			
			policyList = (List<Policy>)query.list();
			
		} catch (Exception e) {
			
			e.printStackTrace();
			
		} finally {
			
			if(session != null && session.isOpen())
				session.close();
		}
		return policyList;
	}
	
	public List<Policy> getPolicyList(String employeeId, Integer year, Integer month, Integer pageNumber, Integer pageSize)
	{
		String hql = null;
		boolean flag;
		if(employeeId == null || employeeId.trim().length() == 0)
		{
			hql = "from Policy policy where policy.isEffect=true and year(policy.insuranceDate)=? and month(policy.insuranceDate)=?";
			flag = false;
		}
		else
		{
			hql = "from Policy policy where policy.isEffect=true and year(policy.insuranceDate)=? and month(policy.insuranceDate)=? and policy.employee.employeeId=?";
			flag = true;
		}

		List<Policy> policyList = null;
		Session session = null;
		try {

			session = HibernateSessionFactory.getSession();
			Query query = session.createQuery(hql);
			query.setInteger(0,year);
			query.setInteger(1,month);
			if(flag)
				query.setString(2,employeeId);

			query.setFirstResult((pageNumber - 1) * pageSize);
			query.setMaxResults(pageSize);
			
			policyList = (List<Policy>)query.list();
			
		} catch (Exception e) {
			
			e.printStackTrace();
			
		} finally {
			
			if(session != null && session.isOpen())
				session.close();
		}
		return policyList;	
	}
}